package me.linkcn.ydtxl.server.data.jdbc;

import me.linkcn.ydtxl.server.data.DataFacade;
import me.linkcn.ydtxl.server.objects.Account;
import me.linkcn.ydtxl.server.objects.Group;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * JDBC门面.
 * User: cmcc
 * Date: 11-8-14
 * Time: 下午10:06
 */
public class JdbcFacade extends DataFacade {
    protected Connection conn;

    public JdbcFacade(String dbUri) {
        try {
            DataSource source = (DataSource) new InitialContext().lookup(dbUri);
            this.conn = source.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Account createAccount(Account account) {
        account.setGid(Long.toString(new Date().getTime()));
        return account;
    }

    @Override
    public Account getAccountByLoginPwd(String identifier, char[] secret) {
        String query = "SELECT gid, account_name, account_type, password FROM accounts WHERE account_name=? AND password=?";
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, identifier);
            preparedStatement.setString(2, new String(secret));
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Account account = new Account();
                account.setGid(resultSet.getString(1));
                account.setAccountName(resultSet.getString(2));
                account.setAccountType(resultSet.getString(3));
                account.setPassword(resultSet.getString(4));
                return account;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int generateGroupIdByAccountName(String accountName) {
        int groupId = -1;
        String query = "SELECT max(_id) from contacts_groups WHERE account_name=?";
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, accountName);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                groupId = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return groupId;
    }

    @Override
    public boolean createGroup(Group group) {
        int result = 0;
        String query = "INSERT INTO contacts_groups (gid, _id, package_id, account_name, account_type, sourceid, ver, " +
                "dirty, title, title_res, notes, system_id, deleted, group_visible, should_sync, sync1, sync2, sync3, sync4) " +
                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, group.getGid().trim());
            preparedStatement.setInt(2, group.get_id());
            preparedStatement.setInt(3, group.getPackageId());
            preparedStatement.setString(4, group.getAccountName().trim());
            preparedStatement.setString(5, group.getAccountType().trim());
            preparedStatement.setString(6, group.getSourceid().trim());
            preparedStatement.setInt(7, group.getVersion());
            preparedStatement.setInt(8, group.getDirty());
            preparedStatement.setString(9, group.getTitle().trim());
            preparedStatement.setString(10, group.getTitleRes());
            preparedStatement.setString(11, group.getNotes().trim());
            preparedStatement.setString(12, group.getSystemId().trim());
            preparedStatement.setInt(13, group.getDeleted());
            preparedStatement.setInt(14, group.getGroupVisible());
            preparedStatement.setInt(15, group.getShouldSync());
            preparedStatement.setString(16, group.getSync1().trim());
            preparedStatement.setString(17, group.getSync2().trim());
            preparedStatement.setString(18, group.getSync3().trim());
            preparedStatement.setString(19, group.getSync4().trim());
            //System.out.println(preparedStatement.toString());
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (result > 0) {
            return true;
        } else {
            return false;
        }
    }

    @Override
    public List<Group> getGroupListByAccountName(String accountName) {
        List<Group> groupList = new ArrayList<Group>();
        String query = "SELECT gid, _id, package_id, account_name, account_type, sourceid, ver, dirty, title, " +
                "title_res, notes, system_id, deleted, group_visible, should_sync, sync1, sync2, sync3, sync4 " +
                "FROM contacts_groups WHERE account_name=? ORDER BY _id ASC";
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, accountName);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Group group = new Group();
                group.setGid(resultSet.getString(1).trim());
                group.set_id(resultSet.getInt(2));
                group.setPackageId(resultSet.getInt(3));
                group.setAccountName(resultSet.getString(4));
                group.setAccountType(resultSet.getString(5));
                group.setSourceid(resultSet.getString(6));
                group.setVersion(resultSet.getInt(7));
                group.setDirty(resultSet.getInt(8));
                group.setTitle(resultSet.getString(9));
                group.setTitleRes(resultSet.getString(10));
                group.setNotes(resultSet.getString(11));
                group.setSystemId(resultSet.getString(12));
                group.setDeleted(resultSet.getInt(13));
                group.setGroupVisible(resultSet.getInt(14));
                group.setShouldSync(resultSet.getInt(15));
                group.setSync1(resultSet.getString(16));
                group.setSync2(resultSet.getString(17));
                group.setSync3(resultSet.getString(18));
                group.setSync4(resultSet.getString(19));
                groupList.add(group);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return groupList;
    }

    public Group getGroupByAccountNameAndGroupName(String accountName, String groupName) {
        Group group = null;
        String query = "SELECT gid, _id, package_id, account_name, account_type, sourceid, ver, dirty, title, " +
                "title_res, notes, system_id, deleted, group_visible, should_sync, sync1, sync2, sync3, sync4 " +
                "FROM contacts_groups WHERE account_name=? AND title=?";
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, accountName);
            preparedStatement.setString(2, groupName);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                group = new Group();
                group.setGid(resultSet.getString(1).trim());
                group.set_id(resultSet.getInt(2));
                group.setPackageId(resultSet.getInt(3));
                group.setAccountName(resultSet.getString(4));
                group.setAccountType(resultSet.getString(5));
                group.setSourceid(resultSet.getString(6));
                group.setVersion(resultSet.getInt(7));
                group.setDirty(resultSet.getInt(8));
                group.setTitle(resultSet.getString(9));
                group.setTitleRes(resultSet.getString(10));
                group.setNotes(resultSet.getString(11));
                group.setSystemId(resultSet.getString(12));
                group.setDeleted(resultSet.getInt(13));
                group.setGroupVisible(resultSet.getInt(14));
                group.setShouldSync(resultSet.getInt(15));
                group.setSync1(resultSet.getString(16));
                group.setSync2(resultSet.getString(17));
                group.setSync3(resultSet.getString(18));
                group.setSync4(resultSet.getString(19));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return group;
    }

    public boolean deleteGroupByAccountNameAndGroupName(String accountName, String groupName) {
        int result = 0;
        String query = "DELETE FROM contacts_groups WHERE account_name=? AND title=?";
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, accountName);
            preparedStatement.setString(2, groupName);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (result > 0) {
            return true;
        } else {
            return false;
        }
    }
}
